﻿using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace yichu_front.BLL
{
    public static class BOMService
    {
        public static void initDBToMode()
        { 
        //SqlSugarHelp.scope.DbFirst.Where("BOMList").IsCreateAttribute().CreateClassFile("D:\\项目\\易初\\代码\\yichu_manager\\yichu_manager\\Model", "yichu_manager");
            SqlSugarHelp.scope.DbFirst.Where("BOMList_Material_Dosage").IsCreateAttribute().CreateClassFile("D:\\项目\\易初\\代码\\yichu_manager\\yichu_manager\\Model", "yichu_manager");
        }
        /// <summary>
        /// 根据型号的id获取该型号所有的规格
        /// </summary>
        /// <param name="bOMModelid"></param>
        /// <returns></returns>
        public static DataTable getBOMListDataTableById(int bOMModelid)
        {
            //return SqlSugarHelp.scope.Ado.GetDataTable("select id, bomModelId, 序号, 规格, 单价, 编码, 导体折数, 外径下限, 标称外径, 外径上限, 额定电压, 导体电阻, 耐压等级, 近似重量 from BOMList where bomModelId=@id and 状态 = '已审核' order by  序号", new { id = bOMModelid });
            //return SqlSugarHelp.scope.Ado.GetDataTable("select 0 选择, id, bomModelId,  ROW_NUMBER() OVER (ORDER BY 修改时间 ,规格) 序号,规格, 单价, 编码, 导体折数, 外径下限, 标称外径, 外径上限, 额定电压, 导体电阻, 耐压等级, 近似重量 from BOMList where bomModelId=@id and 状态 = '已审核' order by  序号", new { id = bOMModelid });
            return SqlSugarHelp.scope.Ado.GetDataTable("select 0 选择, id, bomModelId,  ROW_NUMBER() OVER (ORDER BY 序号)  序号,规格, 单价, 编码, 导体折数, 外径下限, 标称外径, 外径上限, 额定电压, 导体电阻, 耐压等级, 近似重量 from BOMList where bomModelId=@id and 状态 = '已审核' order by  序号", new { id = bOMModelid });

        }
        /// <summary>
        /// 根据型号ID查询最大的序号
        /// </summary>
        /// <param name="bOMModelId"></param>
        /// <returns></returns>
        internal static int? getMax序号(int bOMModelId)
        {
            return SqlSugarHelp.scope.Queryable<BOMList>().Where(it => it.bomModelId == bOMModelId).Max(it => it.序号);
        }

        /// <summary>
        /// 根据修改的列更新BOM规格的基础数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="columnsName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static int updateBOMListBasicDataById(int id,string columnsName,string value)
        {
            // 字典
            var dt = new Dictionary<string, object>();
            dt.Add("id", id);
            dt.Add(columnsName, value);
            return SqlSugarHelp.scope.Updateable(dt).AS("BOMList").WhereColumns("id").ExecuteCommand();
        }
        /// <summary>
        /// 修改BOM型号
        /// </summary>
        /// <param name="bOMModel"></param>
        /// <returns></returns>
        internal static int editBOMModel(BOMModel bOMModel)
        {
            return SqlSugarHelp.scope.Updateable(bOMModel).UpdateColumns(it => new { it.型号, it.修改时间 }).ExecuteCommand();
        }

        /// <summary>
        /// 添加型号
        /// </summary>
        /// <param name="bOMModel"></param>
        /// <returns></returns>
        public static int addBOMModel(BOMModel bOMModel)
        {
            return SqlSugarHelp.scope.Insertable(bOMModel).ExecuteCommand();
        }

        internal static int deleteBOMModelById(BOMModel bOMModel)
        {
            return SqlSugarHelp.scope.Deleteable<BOMModel>().Where(bOMModel).ExecuteCommand();
        }
        /// <summary>
        /// 根据BOMModelId查询现在使用的材料
        /// </summary>
        /// <param name="bOMModelID"></param>
        internal static List<Material> getBOMModelD_MaterialByBOMModelID(int bOMModelID)
        {
           return SqlSugarHelp.scope.Queryable<BOMModel_Material>()
            .LeftJoin<Material>((bm, m) => bm.materialId == m.id)
            .Where(bm => bm.BOMModelId == bOMModelID)
            .Select((bm, m) => new Material { id = m.id,序号 = m.序号, 材料名称 = m.材料名称, 材料单价 = m.材料单价, 偏移量 = m.偏移量 })
            .ToList(); 
        }

        //public static List<BOMList> GetBOMListByBOMModelId(int BOMModelId)
        //{
        //    //三个表联查
        //    //return SqlSugarHelp.scope.Queryable<BOMList>().Where(bomList => bomList.bomModelId == BOMModelId)
        //    //                                              .Mapper((bomList) => {bomList.BOMList_Materials = SqlSugarHelp.scope.Queryable<BOMList_Material>()
        //    //                                                                                                .Where(bomList_material => bomList_material.BOMListId == bomList.id)
        //    //                                                                                                 .Mapper((bomList_material) => bomList_material.material = SqlSugarHelp.scope.Queryable<Material>()
        //    //                                                                                                                                                            .Where(material => material.id == bomList_material.materialId).First()).ToList();
        //    //}).ToList();
        //    //只查BOMList
        //}
        /// <summary>
        /// 根据规格ID查询材料,得到表头
        /// </summary>
        public static List<BOM_Material_Mapping> getMaterial(int BOMModelId,int BOMListId)
        {
            return SqlSugarHelp.scope.Queryable<BOMModel_Material>()
                           .LeftJoin<Material>((b, m) => b.materialId == m.id)
                           .Where(b => b.BOMModelId == BOMModelId)
                           .Select((b, m) => 
                                                                            new BOM_Material_Mapping {material_Id = m.id, BOMModel_Material_Id  = b.id, 材料名称 = m.材料名称 
                                                                                                        , 材料单价 = m.材料单价, 偏移量 = m.偏移量})
                           .ToList();

            //行转列
            //var  a =  SqlSugarHelp.scope.Queryable<BOMModel_Material>()
            //                .LeftJoin<Material>((bom_mal, m) => bom_mal.materialId == m.id)
            //                .LeftJoin<BOMList_Material_Dosage>((bom_mal,m,bmd) => bom_mal.id == bmd.BOMModel_MaterialId)
            //                .Where((bom_mal,m,bmd) => bom_mal.BOMModelId == BOMModelId && bmd.BOMListId == BOMListId)
            //                .Select((bom_mal, m, bmd) => new { 材料名称 = m.材料名称, 材料用量 = bmd.材料用量, name = "合计" })
            //                .ToPivotTable(it => it.材料名称, it => it.name, it => it.Sum(x => x.材料用量));
            //var a = SqlSugarHelp.scope.Queryable<BOMModel_Material>()
            //                .LeftJoin<Material>((b, m) => b.materialId == m.id)
            //                .Where(b => b.BOMModelId == BOMModelId)

            //                .GroupBy((b, m) => m.材料名称)
            //                .Select((b, m) => new { 材料名称 = m.材料名称, 材料用量 = SqlFunc.AggregateSum(b.材料用量), name = "材料用量" })
            //.ToPivotTable(it => it.材料名称,it => it.name, it => it.Sum(x => x.材料用量));  //ViewOrder是一个新建的类，更多Select用法看下面文档
            //return a;
        }
        /// <summary>
        /// 查询材料用量。如果没有设置材料用量就默认为0
        /// </summary>
        /// <param name="bomListId"></param>
        /// <param name="bOMModel_MaterialId"></param>
        /// <param name="材料id"></param>
        /// <returns></returns>
        internal static decimal? getMaterialDosage(int bomListId, int bOMModel_MaterialId, int 材料id)
        {
             return SqlSugarHelp.scope.Queryable<BOMList_Material_Dosage>().Where(it => it.BOMListId == bomListId && it.BOMModel_MaterialId == bOMModel_MaterialId).Select(it => it.材料用量).First();
        }

        /// <summary>
        /// 更新材料用量
        /// </summary>
        /// <param name="item">使用的材料</param>
        /// <param name="dosage">更改后的材料用量</param>
        internal static int updateBOMListMaterialDosage(BOM_Material_Mapping item, int BOMListId, decimal dosage)
        {
            int i = 0;
            //检查用量表里有没有对应的用量，如果没有则创建
            if (SqlSugarHelp.scope.Queryable<BOMList_Material_Dosage>().Any(it => it.BOMListId == BOMListId && it.BOMModel_MaterialId == item.BOMModel_Material_Id))
            {//有用量行了
               i = SqlSugarHelp.scope.Updateable<BOMList_Material_Dosage>()
                                    .SetColumns(it => it.材料用量 == dosage)
                                    .Where(it => it.BOMListId == BOMListId && it.BOMModel_MaterialId == item.BOMModel_Material_Id)
                                    .ExecuteCommand();
            }
            else
            {//没有用量行,新建一条
                BOMList_Material_Dosage BOMList_Material_Dosage = new BOMList_Material_Dosage();
                BOMList_Material_Dosage.BOMListId = BOMListId;
                BOMList_Material_Dosage.BOMModel_MaterialId = item.BOMModel_Material_Id;
                BOMList_Material_Dosage.材料用量 = dosage;
                i = SqlSugarHelp.scope.Insertable(BOMList_Material_Dosage).ExecuteCommand();
            }
            return i;
        }
        /// <summary>
        /// 产品型号选择材料，将modelId和材料id插入中间表
        /// </summary>
        /// <param name="bOMModelId"></param>
        /// <param name="material"></param>
        internal static int addBOMModel_Material(BOMModel_Material bOMModel_Material)
        {
            return SqlSugarHelp.scope.Insertable(bOMModel_Material).ExecuteCommand();
        }
        /// <summary>
        /// 根据BOMListId删除
        /// </summary>
        /// <param name="v"></param>
        /// <returns></returns>
        internal static int deleteBOMListByid(int BOMListId)
        {
            return SqlSugarHelp.scope.Deleteable<BOMList>().Where(it => it.id == BOMListId).ExecuteCommand();
        }
    }
}
